library(plyr)
library(data.table)

dataDir <- "C:/UKHR/CSV"

LoadData <- function (dataDir) {
  # Loads data from a directory full of csv files
  # Args:
  #   dataDir: File path of directory as string  
  
  #TODO - check for existing data, append if exists
  fileList = list.files(dataDir, full.names = TRUE, pattern = "[a-z]*.csv$")
  d <- ldply(fileList, .fun = read.csv, header = TRUE)
  d <- data.table(d)
  setkey(d, UKHR_RaceID, Horse)
  d <- unique(d)    #remove duplicates (as per key)
  return(d)
}

#TO FIX:
#due to memory issues when loading data, combine the csvs
#using concat.bat, fix the header line in Excel then import
d <- LoadData(dataDir, d)
d <- CleanData
gc()    #run a garbage clean once in a while

d[ ,unique(UKHR_RaceID)]  #example: extract unique race IDs
d[J(153386),]             #example: find all race ID = 153384
d[Meeting == "Kempton" & Date == 15/10/2008,]     #example: find all races at Kempton on 15/10/2008
                                                  #vector search, poss faster method, see data.table docs
names(d)                  #example: get header names

value <- d[ , sum(Value.Odds..Probability.),by=UKHR_RaceID]    #extract sum of value for each market
setnames(value, "V1", "Total.Mkt.Value")
#plot histogram of sums
hist(value[ ,Total.Mkt.Value],
     breaks = 500,
     xlim   = c(0.5,1.1),
     main   = "UKHR - Total value probabilities (per market)",
     xlab   = "Total market value probability")

system.time (select <- d[Ratings.Position <= 3, ])    #select top three rated, time it
system.time (select <- d[ ,Ratings.Position <= 3])    #a lot faster, but just list of booleans
system.time (select <- d[d[ ,Ratings.Position <= 3]]) #slow again - is there another way?

CleanData <- function(d) {
  
  # Args:
  #   Data: Name of existing data file 
  
  # check if columns exist
  if !("Total.Mkt.Value" %in% names(d)) (d <- d[ , Total.Mkt.Value := NA  ]
  if !("Value.Odds..Probability.Norm" %in% names(d)) (d <- d[ , Value.Odds..Probability.Norm := NA  ]                                       
                                                                                             
  # Adds the sum of the value probs per market
  tt <- d[ ,list(Total.Mkt.Value = sum(Value.Odds..Probability.),
            Value.Odds..Probability.Norm = Value.Odds..Probability. / sum(Value.Odds..Probability.)),
            by=UKHR_RaceID]
  d <- cbind(d, tt[ , list(Total.Mkt.Value, Value.Odds..Probability.Norm)])
  remove(tt)
                                                                                             
  #Stall percentage - Remove % sign and convert to numeric
  d[ , Stall.Percentage.Fixed := d[ ,as.numeric(sub("%", "", Stall.Percentage))]]
  return(d)
}

q = quote(Ratings.Position <= 3)        #write selection code outside of data table function
select <- d[d[ , eval(q)]]

select.Cassiopeia = quote(
  Country == "GB" &
  Ratings.Position <= 2 &
  Race.Type == "Flat" &
  Class.Diff..Difference > 0 &
  Betfair.Win.S.P. > (1 / Value.Odds..Probability.) &
  Stall.Percentage.Fixed >= 10
)

select <- d[eval(select.Cassiopeia), ]  #equivalent to the output dump

system.time(select[ , P_L := -1])
system.time(select[Result == 1 , P_L := Betfair.Win.S.P. - 1])

system.time(select[ , ifelse(Result == 1, Betfair.Win.S.P. - 1, -1)]) #no difference

select[ , sum(Result == 1)]               #wins
select[ , list(P_L = round(sum(P_L), 1)), by=Meeting]    #wins by meeting
select[ , list(P_L = round(sum(P_L), 1)), by=list(Meeting, Handicap)]

select[ , list(Runs = dim(.SD)[1],         #.SD is the subset selected by "by", in this case Meeting
               Wins = sum(Result == 1),
               Profit = round(sum(P_L), 1),
               SR = round(100 * sum(Result == 1) / dim(.SD)[1], 1),   #duplication
               ROI = round(100 * sum(P_L) / dim(.SD)[1], 1)
               ),
       by = Meeting]

#create a report function to simplify muliple report making
MakeReport <- function(selectionData, byCriteria){
  #byCriteria as string (or list of strings)
  selectionData[ , list(P_L = round(sum(P_L), 1)), by=byCriteria]
}
  
MakeReport(select, "Meeting")
MakeReport(select, list("Meeting", "Handicap"))   #error
MakeReport(select, c("Meeting", "Handicap"))      #seems to want vector rather than a list, but works

lReports <- list("Meeting", "Handicap", c("Meeting", "Handicap"))   #let's try generating multiple reports
MakeReport(select, lReports[1])         #error
MakeReport(select, lReports[[1]])
MakeReport(select, lReports[[3]])      

for (i in length(lReports)) {
  reports <- MakeReport(select, lReports[[i]])
}
#saves last report, need to combine selection columns if want to join the reports